package org.linsoho.xmlupdate;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DBHelper {
    
    private final static Logger LOG = LoggerFactory.getLogger(DBHelper.class);
    
    private static final String classNm = "org.postgresql.Driver";
    private static String url;
    private static String user;
    private static String password;
    
    private static Connection conn;
    private static PreparedStatement pst;
    
    private static DBHelper instance;
    
    public static DBHelper getInstance() {
        if (instance == null) {
            instance = new DBHelper();
        }
        return instance;
    }
    
    private DBHelper() {
        PropertyReader prop = new PropertyReader();
        url = prop.getProperty("url");
        user = prop.getProperty("user");
        password = prop.getProperty("password");
    }
    
    public ResultSet query(String sql) {
        ResultSet set = null;
        try {
            LOG.info("Start sql query!");
            LOG.info("running sql:", sql);
            Class.forName(classNm);
            conn = DriverManager.getConnection(url, user, Utils.StringToMd5(password));
            pst = conn.prepareStatement(sql);
            set = pst.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return set;
    }
    
    public boolean update(String sql) {
        try {
            LOG.info("Start sql query!");
            LOG.info("running sql:", sql);
            Class.forName(classNm);
            conn = DriverManager.getConnection(url, user, password);
            pst = conn.prepareStatement(sql);
            pst.executeUpdate();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
    public void close() {
        try {
            if (conn != null) {
                conn.close();
            }
            if (pst != null) {
                pst.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
